package com.wy17113.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;

import com.wy17113.controller.utils.DBConnection;
import com.wy17113.model.dao.ILifeInfoDao;
import com.wy17113.model.entity.Common;
import com.wy17113.model.entity.LifeInfo;

public class LifeInfoDaoImpl extends HibernateBaseDao implements ILifeInfoDao {

	@Override
	public Class<LifeInfo> getEntityClass() {
		return LifeInfo.class;
	}

	@Override
	public String getEntityType() {
		return "LifeInfo";
	}

	public void saveLifeInfo(LifeInfo li) throws SQLException {
		getHibernateTemplate().save(li);
	}

	public int getLifeInfoListCounts(int typeId,String userId,String flag) {
		Session session = getSession(true);		
		String hql = "SELECT COUNT(id) as counts FROM LifeInfo li WHERE 1 =1";
		
		if(flag.equals("2")){
			hql = hql + " AND li.state = 1 ";
		}else{
			hql = hql + " AND li.state = " + Integer.parseInt(flag);
		}
		if(typeId != 0){
			hql = hql + " AND li.typeId = " + typeId;
		}
		
		Query q = session.createQuery(hql);
		Integer count=((Number)q.uniqueResult()).intValue();		
		return count;
	}

	public List<LifeInfo> getLifeInfoList(int typeId,String userId, int startIndex,
			int pageRowCount,String flag) {		
		String sql = "SELECT * FROM lifeInfos li WHERE 1 =1";		
		Connection conn = DBConnection.getConnection();
		
		if(flag.equals("2")){
			sql = sql + " AND li.state = " + new Integer(1);
		}else{
			sql = sql + " AND li.state = " + Integer.parseInt(flag);
		}
		
		if (typeId != 0){
			sql = sql + " AND li.typeId = " + typeId;
		}
		
		sql = sql + " ORDER BY updateDate desc,sort desc,subDate desc LIMIT " + startIndex + "," + pageRowCount;
		
		List<LifeInfo> lifeInfoList = new ArrayList<LifeInfo>();;
		try {
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet set = stmt.executeQuery();
			
			LifeInfo li = null;
			while (set.next()) {
				li = new LifeInfo();
				li.setId(set.getString(1));
				li.setName(set.getString(2));
				li.setContent(set.getString(3));
				li.setTypeId(set.getInt(4));
				
				li.setKindId(set.getInt(5));
				li.setPrice(set.getString(6));
				li.setSort(set.getInt(7));
				li.setLocation(set.getString(8));
				
				li.setUserId(set.getString(9));
				li.setState(set.getInt(10));
				li.setSubDate(set.getString(11));
				li.setMemo(set.getString(12));
				
				lifeInfoList.add(li);
			}
			
			stmt.close();
		    conn.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return lifeInfoList;
	}

	public LifeInfo getLifeInfoById(String id) {
		return (LifeInfo)getHibernateTemplate().get(getEntityClass(), id);
	}

	public void deleteLifeInfos(String[] ids) {
		for(String id : ids){
			getHibernateTemplate().delete(getHibernateTemplate().get(getEntityClass(), id));
		}
	}

	public void shenHeLifeInfos(String[] ids) throws Exception {
		String sql = "";		
		Connection conn = DBConnection.getConnection();
		PreparedStatement stmt = null;		
		
		for(String id : ids){
			try {
				sql = "UPDATE lifeInfos SET state = 1 WHERE	id='"+ id +"'";	
				stmt = conn.prepareStatement(sql);
				stmt.executeUpdate();				
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		
		stmt.close();
	    conn.close();		
	}

	public void zhiDingLifeInfo(String id) {
		String sql = "UPDATE lifeInfos SET sort = " + (getMaxSort() + 1) + " WHERE id='"+ id +"'";		
		Connection conn = DBConnection.getConnection();
		PreparedStatement stmt = null;		
		
		try {
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();
			stmt.close();
		    conn.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
	
	public int getMaxSort(){
		Session session = getSession(true);		
		String hql = "SELECT max(sort) as sort FROM LifeInfo";
				
		Query q = session.createQuery(hql);
		Integer count = ((Number)q.uniqueResult()).intValue();		
		return count;
	}
	
	public int getMaxCommonSort(String infoId){
		Session session = getSession(true);		
		String hql = "SELECT max(sort) as sort FROM Common WHERE infoId = '"+ infoId +"'";
				
		Query q = session.createQuery(hql);
		Integer count = ((Number)(q.uniqueResult() != null ? q.uniqueResult() : 0)).intValue();		
		return count;
	}

	public List<Common> getCommonListByInfoId(String infoId, int startIndex,
			int pageRowCount) throws Exception {
		String sql = "SELECT * FROM commons com WHERE 1 =1";		
		Connection conn = DBConnection.getConnection();
		
		if(null != infoId && !"".equals(infoId)){
			sql = sql + " AND com.infoId = '" + infoId + "'";
		}
		
		sql = sql + " ORDER BY sort asc LIMIT " + startIndex + "," + pageRowCount;
		
		List<Common> commonList = new ArrayList<Common>();;
		try {
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet set = stmt.executeQuery();
			
			Common com = null;
			while (set.next()) {
				com = new Common();
				com.setId(set.getString(1));
				com.setInfoId(set.getString(2));
				com.setContent(set.getString(3));
				com.setState(set.getInt(4));
				com.setSort(set.getInt(5));
				com.setSubDate(set.getString(6));
								
				commonList.add(com);
			}
			
			stmt.close();
		    conn.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return commonList;
	}

	public int getCommonCountByInfoId(String infoId) throws Exception {
		Session session = getSession(true);		
		String hql = "SELECT COUNT(id) as counts FROM Common com WHERE 1 =1";
		
		if(null != infoId && !"".equals(infoId)){
			hql = hql + " AND com.infoId = '" + infoId + "'";
		}
		
		Query q = session.createQuery(hql);
		Integer count=((Number)q.uniqueResult()).intValue();		
		return count;
	}

	public void saveCommon(Common com) throws SQLException {
		save(com);
	}

	public void deleteCommons(String[] ids) {
		for(String id : ids){
			getHibernateTemplate().delete(getHibernateTemplate().get(getCommonClass(), id));
		}
	}
	
	public Class<Common> getCommonClass() {
		return Common.class;
	}

	public void updateClicksById(String infoId) {
		String sql = "";		
		Connection conn = DBConnection.getConnection();
		PreparedStatement stmt = null;		
		
		try {
			sql = "UPDATE lifeInfos SET kindId = kindId + 1 WHERE id='"+ infoId +"'";	
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();		

			stmt.close();
		    conn.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}

	public void updateLifeInfoUpdateDate(String infoId,String updateDate) {
		String sql = "";		
		Connection conn = DBConnection.getConnection();
		PreparedStatement stmt = null;		
		
		try {
			sql = "UPDATE lifeInfos SET updateDate = '"+  updateDate +"' WHERE id='"+ infoId +"'";	
			stmt = conn.prepareStatement(sql);
			stmt.executeUpdate();		

			stmt.close();
		    conn.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
}
